package com.augurit.gzcgwsp.wfex.bi.service.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springside.modules.orm.Page;
import org.springside.modules.orm.PropertyFilter;

import com.augurit.common.system.web.auth.form.LoginUserForm;
import com.augurit.common.util.HqlUtils;
import com.augurit.common.util.PageUtils;
import com.augurit.common.util.lang.StringUtils;
import com.augurit.gzcgwsp.wfex.bi.convert.WfTemplateAttachmentRefConvertorEX;
import com.augurit.gzcgwsp.wfex.bi.service.IWfTemplateAttachmentRefServiceEX;
import com.augurit.gzcgwsp.wfex.bi.web.form.WfTemplateAttachmentRefFormEX;
import com.augurit.gzcgwsp.wfex.common.dao.WfTemplateAttachmentRefDaoEX;
import com.augurit.gzcgwsp.wfex.common.entity.WfTemplateAttachmentRefEX;
import com.augurit.gzcgwsp.ywcmn.web.form.YwComPrintForm;
import com.augurit.wf.bi.convert.WfTemplateAttachmentRefConvertor;
import com.augurit.wf.bi.service.IWfActPrinttplPrivDefService;
import com.augurit.wf.bi.service.IWfBusTemplateService;
import com.augurit.wf.bi.service.IWfTemplateAttachmentRefService;
import com.augurit.wf.bi.web.form.WfTemplateAttachmentRefForm;
import com.augurit.wf.common.dao.WfTemplateAttachmentRefDao;
import com.augurit.wf.common.entity.WfTemplateAttachmentRef;

@Service("wfTemplateAttachmentRefServiceImplEX")
@Transactional
public class WfTemplateAttachmentRefServiceImplEX implements IWfTemplateAttachmentRefServiceEX {

	@Autowired
	private IWfBusTemplateService wfBusTemplateService;
	
	@Autowired
	private IWfActPrinttplPrivDefService WfActPrinttplPrivDefServiceImpl;
	
	@Autowired
	private WfTemplateAttachmentRefDao wfTemplatePrintDao;
	
	@Autowired
	private WfTemplateAttachmentRefDaoEX wfTemplatePrintDaoEX;
	
	@Autowired
	private IWfTemplateAttachmentRefService wfTemplateAttachmentRefService;
	
	public IWfTemplateAttachmentRefService getWfTemplateAttachmentRefService(){
		return this.wfTemplateAttachmentRefService;
	}
	/**
     * 获取打印模板
     * @param templateCode
     * @return
     */
    public String getPrintTemplatePath(Long id){
        if(id != null && id != 0){
            String hql = " from WfTemplateAttachmentRefEX ref where ref.id = ?";
            
            WfTemplateAttachmentRefEX entity = wfTemplatePrintDaoEX.findUnique(hql, id);
            return entity.getFilePath() + "/" + entity.getFileRealName();
        }
        return null;
    }
    /**
     * 获取打印模板
     * @param templateCode
     * @return
     */
    public String getPrintTemplatePath(String templateCode, String fileType){
        if(templateCode != null && templateCode.trim().length() > 0){
            Long templateId = wfBusTemplateService.getByTemplateCode(templateCode).getId();
            String hql = " from WfTemplateAttachmentRefEX ref where ref.templateId = ? and ref.fileType = ?";
            
            WfTemplateAttachmentRefEX entity = wfTemplatePrintDaoEX.findUnique(hql, templateId, fileType);
            return entity.getFilePath() + "/" + entity.getFileRealName();
        }
        return null;
    }
  
    /**
	 * 显示模板下的所有红头文件
	 * @param templateCode 业务流程模板code
	 * @return
	 */
	public List<WfTemplateAttachmentRefForm> listAllRedHeadFiles(String templateCode){
		if(templateCode != null && templateCode.trim().length() > 0){
			Long templateId = wfBusTemplateService.getByTemplateCode(templateCode).getId();
			String hql = " from WfTemplateAttachmentRef ref where ref.templateId = ? and ref.fileType = ?";
			List<WfTemplateAttachmentRef> entities = wfTemplatePrintDao.find(hql, templateId, WfTemplateAttachmentRefFormEX.RED_HEAD_FILE_TYPE);
			return WfTemplateAttachmentRefConvertor.convertToFormList(entities);
		}
		return null;
	}
	public List<WfTemplateAttachmentRefForm> listRedHeadFilesByPri(String templateCode){
		if(templateCode != null && templateCode.trim().length() > 0){
			Long templateId = wfBusTemplateService.getByTemplateCode(templateCode).getId();
			String hql = " from WfTemplateAttachmentRef ref where ref.templateId = ? and ref.fileType = ?";
			List<WfTemplateAttachmentRef> entities = wfTemplatePrintDao.find(hql, templateId, WfTemplateAttachmentRefFormEX.RED_HEAD_FILE_TYPE);
			return WfTemplateAttachmentRefConvertor.convertToFormList(entities);
		}
		return null;
	}
    /**
	 * 显示模板下的所有正文文件
	 * @param templateCode
	 * @return
	 */
	public List<WfTemplateAttachmentRefForm> listAllZhengWenFiles(String templateCode){
		if(templateCode != null && templateCode.trim().length() > 0){
			Long templateId = wfBusTemplateService.getByTemplateCode(templateCode).getId();
			String hql = " from WfTemplateAttachmentRef ref where ref.templateId = ? and ref.fileType = ?";
			List<WfTemplateAttachmentRef> entities = wfTemplatePrintDao.find(hql, templateId, WfTemplateAttachmentRefFormEX.ZHENGWEN_FILE_TYPE);
			return WfTemplateAttachmentRefConvertor.convertToFormList(entities);
		}
		return null;
	}
	//==============================================//
	public List<WfTemplateAttachmentRefForm> listAllZhengWenFilesByPriv(String templateCode,String activityName,Integer version){
  		if(templateCode != null && templateCode.trim().length() > 0){
			Long templateId = wfBusTemplateService.getByTemplateCode(templateCode).getId();
			String sql="select * from wf_template_attachment_ref a where a.id in ((select  t.id from wf_template_attachment_ref t  where t.template_id = "+templateId+" and t.file_type = "+WfTemplateAttachmentRefFormEX.ZHENGWEN_FILE_TYPE+" )";
				
			if(version!=null)  //把受权限控制的文档去掉
			{   
				sql+="minus  (select distinct f.element_id from wf_act_printtpl_priv_def f where f.version = "+version+" and f.activity_name = '"+activityName+"' and f.template_id = "+templateId+" and f.is_hidden = 1))";
			}else {
				sql+="minus  (select distinct f.element_id from wf_act_printtpl_priv_def f where f.activity_name = '"+activityName+"' and f.template_id = "+templateId+" and f.is_hidden = 1))";
				
			}
			System.out.println("制文:"+sql);
			Connection connnection=wfTemplatePrintDao.getSession().connection();
			Statement statement=null;
			ResultSet rs=null;
			List<WfTemplateAttachmentRef> entities=new ArrayList<WfTemplateAttachmentRef>();
			try {
				statement=connnection.createStatement();
				rs=statement.executeQuery(sql);
				while(rs.next()){
				       String filecode=rs.getString("file_code");
				    //if(!("jbrsl".equals(activityName) && "byxkjds".equals(filecode))){
				        WfTemplateAttachmentRef ref=new WfTemplateAttachmentRef();
                        ref.setFileCode(filecode);
                        ref.setId(rs.getLong("id"));
                        ref.setTemplateId(rs.getLong("template_id"));
                        ref.setFileName(rs.getString("file_name"));
                        ref.setFilePath(rs.getString("file_path"));
                        ref.setFileType(rs.getString("file_type"));
                        entities.add(ref);
				    //}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				try {
					rs.close();
					statement.close();
					connnection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		
//			Long templateId = wfBusTemplateService.getByTemplateCode(templateCode).getId();
//			String hql = " from WfTemplateAttachmentRef ref where ref.templateId = ? and ref.fileType = ?";
//			List<WfTemplateAttachmentRef> entities = wfTemplatePrintDao.find(hql, templateId, WfTemplateAttachmentRefFormEX.ZHENGWEN_FILE_TYPE);
			return WfTemplateAttachmentRefConvertor.convertToFormList(entities);
		}
		return null;
	}
	//==============================================//
	public Page<WfTemplateAttachmentRefForm> searchAvailablePrintTemplate(Page<WfTemplateAttachmentRefForm> page, YwComPrintForm printForm,String activityName){
    	if(StringUtils.isBlank(printForm.getTemplateCode())) return null;
    	
    	Page<WfTemplateAttachmentRef> pg = PageUtils.newPageInstance(page);
    	
    	Long templateId = wfBusTemplateService.getByTemplateCode(printForm.getTemplateCode()).getId();
    	
    	StringBuffer hql = new StringBuffer();
    	
    	if (StringUtils.isNotBlank(printForm.getVersion()) && StringUtils.isNotBlank(printForm.getViewName()) && StringUtils.isNotBlank(printForm.getActivityName())) {
    		hql.append(" select elem from WfTemplateAttachmentRef elem ");
    		hql.append("  where elem.id not in (select priv.elementId ");
    		hql.append("                          from WfActPrinttplPrivDef priv ");
    		hql.append("                         where (priv.isHidden = '1' or priv.isLock = '1') ");
    		hql.append("                           and priv.version = " + printForm.getVersion());
    		hql.append("                           and priv.viewName = '" + printForm.getViewName() + "' ");
    		hql.append("                           and priv.activityName = '" + printForm.getActivityName() + "') ");
    		hql.append("    and (elem.fileType = '1' or (elem.fileType = '2' and exists(select t.id from YwPrintDoc t where t.ywId="+printForm.getFkid()+" and t.fileCode=elem.fileCode and t.templateCode='"+printForm.getTemplateCode()+"'))) and elem.templateId = " + templateId);
    		if("dzgz".equals(activityName) ||activityName.indexOf("gz")>0){
    		    hql.append(" and exists(select t.id from YwPrintDoc t where t.ywId="+printForm.getFkid()+" and t.fileCode=elem.fileCode and t.templateCode='"+printForm.getTemplateCode()+"' and creator is not null and creatDate is not null)" );
    		   
    		}
    		
    		hql.append("  order by elem.fileType, elem.id");
    	} else {
    		//办结显示的文书
    		hql.append(" select elem from WfTemplateAttachmentRef elem");
    		//hql.append("  and elem.fileType in ('1','2') and elem.templateId = " + templateId);
    		hql.append("  where (elem.fileType = '1' or (elem.fileType = '2' and exists(select t.id from YwPrintDoc t where t.ywId="+printForm.getFkid()+" and t.fileCode=elem.fileCode and t.templateCode='"+printForm.getTemplateCode()+"'))) and elem.templateId = " + templateId);
    		hql.append("  order by elem.fileType, elem.id");
    	}
    	
    	List<WfTemplateAttachmentRef> entities = wfTemplatePrintDao.findPage(pg, hql.toString()).getResult();
    	PageUtils.copy(pg, WfTemplateAttachmentRefConvertor.convertToFormList(entities), page);
    	return page;
    }
	
	//城管委修改，开始  xgw
	 /**
	 * 分页查询受理通知书打印记录
	 * @param page
	 * @prama  page
	 * @prama  printForm
	 * @return 
	 */
	public Page<WfTemplateAttachmentRefForm> searchAcceptPrintTemplate(Page<WfTemplateAttachmentRefForm> page, YwComPrintForm printForm){
        if(StringUtils.isBlank(printForm.getTemplateCode())) return null;
        
        Page<WfTemplateAttachmentRef> pg = PageUtils.newPageInstance(page);
        
        Long templateId = wfBusTemplateService.getByTemplateCode(printForm.getTemplateCode()).getId();
        
        StringBuffer hql = new StringBuffer();
        String sendAcceptNotice = printForm.getSendAcceptNotice(); 
        String sendYqNotice = printForm.getSendYqNotice(); 
        	
        hql.append(" select elem from WfTemplateAttachmentRef elem ");
        hql.append("  where elem.fileType = '1' and elem.templateId = " + templateId);
        //注释掉的部分  是受理通知书和延期办理通知书   一起查
//        if(!"null".equals(sendAcceptNotice) && !"null".equals(sendYqNotice) && "1".equals(sendYqNotice) && "1".equals(sendAcceptNotice)){
//            hql.append(" and (elem.fileCode='ssgdyqb' or elem.fileCode='sltzs') ");
//        }else if("null".equals(sendAcceptNotice) || "0".equals(sendAcceptNotice)){
//        	hql.append(" and elem.fileCode='ssgdyqb' ");
//        }else{
//        	hql.append(" and elem.fileCode='sltzs' ");
//        }
        hql.append("  and elem.fileCode='sltzs'  ");//受理通知书
        hql.append("  order by elem.fileType, elem.id");
        
        List<WfTemplateAttachmentRef> entities = wfTemplatePrintDao.findPage(pg, hql.toString()).getResult();
        PageUtils.copy(pg, WfTemplateAttachmentRefConvertor.convertToFormList(entities), page);
        return page;
    }
	public Page<WfTemplateAttachmentRefForm> searchPrintTemplate(
			Page<WfTemplateAttachmentRefForm> page, YwComPrintForm printForm) {
		// TODO Auto-generated method stub
		return null;
	}
	/**
	 * 分页查询延期办理通知书打印记录
	 * @param page
	 * @prama  page
	 * @prama  printForm
	 * @return 
	 */
    public Page<WfTemplateAttachmentRefForm> searchYqPrintTemplate(Page<WfTemplateAttachmentRefForm> page, YwComPrintForm printForm){
        if(StringUtils.isBlank(printForm.getTemplateCode())) return null;
        
        Page<WfTemplateAttachmentRef> pg = PageUtils.newPageInstance(page);
        
        Long templateId = wfBusTemplateService.getByTemplateCode(printForm.getTemplateCode()).getId();
        
        StringBuffer hql = new StringBuffer();
        String sendAcceptNotice = printForm.getSendAcceptNotice(); 
        String sendYqNotice = printForm.getSendYqNotice(); 
        	
        hql.append(" select elem from WfTemplateAttachmentRef elem ");
        hql.append("  where elem.fileType = '1' and elem.templateId = " + templateId);
        hql.append("  and elem.fileCode='ssgdyqb'  ");//受理通知书
        hql.append("  order by elem.fileType, elem.id");
        
        List<WfTemplateAttachmentRef> entities = wfTemplatePrintDao.findPage(pg, hql.toString()).getResult();
        PageUtils.copy(pg, WfTemplateAttachmentRefConvertor.convertToFormList(entities), page);
        return page;
    }
    
  //城管委修改，结束 xgw
	
	//**    zhuhx add     *//
	/**
     * 获取打印模板的相对路径
     * @param templateId 业务模板Id
     * @param busFormPrintCode 业务表单打印代码
     */
    @Transactional(readOnly = true)
    public String getPrintTemplateRelativePath(String templateCode, String fileCode){
        if(fileCode != null && StringUtils.isNotBlank(fileCode)){
            String hql =  "select ref from WfTemplateAttachmentRefEX ref, WfBusTemplate temp where ref.templateId = temp.id and ref.fileCode = ? and  temp.templateCode = ?";
            WfTemplateAttachmentRefEX entity = wfTemplatePrintDao.findUnique(hql, fileCode, templateCode);
            return entity.getFilePath() + "/" + entity.getFileRealName();
        }
        return null;
    }
    
    /**
	 * 
	 * 根据主键id获取业务流程打印参考form对象
	 * @param id 主键id
	 * 
	 */
	@Transactional(readOnly = true)
	public WfTemplateAttachmentRefFormEX get(Long id) {
		if(id != null){
			StringBuilder hql = new StringBuilder("select ref, temp.templateCode");
			hql.append(" from WfTemplateAttachmentRefEX ref, WfBusTemplate temp where ref.templateId = temp.id and ref.id = ?");
			Object[] objs =  (Object[])wfTemplatePrintDaoEX.findUnique(hql.toString(), id);
			
			if(objs != null){
				WfTemplateAttachmentRefEX entity = (WfTemplateAttachmentRefEX)objs[0];
				String templateCode = (String)objs[1];
				WfTemplateAttachmentRefFormEX form = WfTemplateAttachmentRefConvertorEX.convertVoToForm(entity);
				form.setTemplateCode(templateCode);
				return form ;
			}
		}
		return null;
		
	}
	
	public Page<WfTemplateAttachmentRefFormEX> search(
			Page<WfTemplateAttachmentRefFormEX> page, List<PropertyFilter> filters) {
		
		return null;
	}
	
	/**
	 * 分页查询业务流程打印参考记录
	 * @param page
	 * @prama  form
	 * @return 
	 */
	@Transactional(readOnly = true)
	public Page<WfTemplateAttachmentRefFormEX> search(Page<WfTemplateAttachmentRefFormEX> page, WfTemplateAttachmentRefFormEX form) {
		
		Page<WfTemplateAttachmentRef> pg = PageUtils.newPageInstance(page);
		
		String hql = " select ref, temp.templateCode from WfTemplateAttachmentRefEX ref, WfBusTemplate temp where ref.templateId = temp.id";
		
		List values = new ArrayList();
		if(form != null){
			if(form.getTemplateId() != null){
				hql += " and ref.templateId = ?";
				values.add(form.getTemplateId());
			}
		}
		
		//排序
		hql += HqlUtils.buildOrderBy(page, "ref");
		
		List objs = wfTemplatePrintDao.findPage(pg, hql, values).getResult();
		PageUtils.copy(pg, WfTemplateAttachmentRefConvertor.convertObjsToFormList(objs), page);
		return page;
	}
	
	public void delete(Long... ids) {
		wfTemplatePrintDao.delete(ids);
	}
	
	public void save(WfTemplateAttachmentRefFormEX... forms) {
		if(forms != null)
			for(WfTemplateAttachmentRefFormEX form : forms)
				this.save(form);
	}
	/**
     * 保存新增或修改的Form对象
     */
    public void save(WfTemplateAttachmentRefFormEX form){
        
        if(form != null){
            WfTemplateAttachmentRefEX entity = null;
            
            //准备VO对象
            if(form != null && form.getId() != null){
                entity = wfTemplatePrintDaoEX.get(form.getId());
                
            }else{
                entity = new WfTemplateAttachmentRefEX();
            }
            //属性值转换
            WfTemplateAttachmentRefConvertorEX.convertFormToVo(form, entity);
            
            //保存
            wfTemplatePrintDaoEX.save(entity);
            
            //回填ID属性值
            form.setId(entity.getId());
        }
    }
    //**    zhuhx end     *//
	public WfTemplateAttachmentRefFormEX getFormByCode(String fileCode,Long tempId) {
		 String hql = " from WfTemplateAttachmentRefEX ref where ref.fileCode ='"+fileCode+"'"
		 				+" and ref.templateId="+tempId;
		 List<WfTemplateAttachmentRefEX> list=this.wfTemplatePrintDaoEX.find(hql);
		 if(list.size()>0){
			 WfTemplateAttachmentRefEX  entity=list.get(0);
			 return WfTemplateAttachmentRefConvertorEX.convertVoToForm(entity);
			
		 }
		 return null;
	}
	/*
	 * (non-Javadoc)
	 * @see com.augurit.gzcgwsp.wfex.bi.service.IWfTemplateAttachmentRefServiceEX#getRedFile(java.lang.Long)
	 */
	@Override
	public WfTemplateAttachmentRefFormEX getRedFile(Long tempId) {
		 String hql = " from WfTemplateAttachmentRefEX ref where ref.fileCode ='RedHead02' and ref.templateId ="+tempId;
			
		 List<WfTemplateAttachmentRefEX> list=this.wfTemplatePrintDaoEX.find(hql);
		 if(list.size()>0){
			 WfTemplateAttachmentRefEX  entity=list.get(0);
			 return WfTemplateAttachmentRefConvertorEX.convertVoToForm(entity);
		 }else{
			 //fileType='4' RedHead02表示要打印的红头 ，
			  hql = " from WfTemplateAttachmentRefEX ref where ref.fileCode ='RedHead02' and ref.fileType='4'";
			  List<WfTemplateAttachmentRefEX> datas=this.wfTemplatePrintDaoEX.find(hql);
			  WfTemplateAttachmentRefEX  entity=datas.get(0);
				 return WfTemplateAttachmentRefConvertorEX.convertVoToForm(entity);
		 }
	
	}
	/*
	 * (non-Javadoc)
	 * @see com.augurit.gzcgwsp.wfex.bi.service.IWfTemplateAttachmentRefServiceEX#getQuRedHeadFiles(com.augurit.gzcgwsp.wfex.bi.web.form.WfTemplateAttachmentRefFormEX)
	 */
	@Override
	public List<WfTemplateAttachmentRefForm> getQuRedHeadFiles(
			WfTemplateAttachmentRefFormEX form,LoginUserForm userForm) {
		String hql = " from WfTemplateAttachmentRef ref where ref.fileCode ='RedHead02' and ref.templateId =0" +
			" and ref.fileName like '%"+userForm.getOrg().getOrgName().substring(0,3)+"%'";
		List<WfTemplateAttachmentRef> entities = wfTemplatePrintDao.find(hql);
		 if(entities.size()>0){
			 return WfTemplateAttachmentRefConvertor.convertToFormList(entities);
		 }
		return null;
	}
	
	
}
